Welcome to "Querying the Data." After watching this video, you will be able
to: Interpret an entity-relationship diagram,
or ERD, for a star schema and use the relations between tables to set up queries. Create a materialized view by denormalizing,
or joining tables from, a star schema. Apply the CUBE and ROLLUP options in a GROUP
BY clause to generate commonly requested total and subtotal summaries. CUBE and ROLLUP operations generate the kinds
of summaries that management often requests. These summaries are much easier to implement
than the multiple SQL queries that are otherwise required. Materialized views conveniently enable you
to create a stored table you so can refresh on a schedule or on-demand. When the a view is complex, requested frequently,
or is run on large data sets, consider materializing the view to help reduce the load on the database. Because the data is precomputed, querying
materialized views can be much faster than querying the underlying tables. Combining cubes or rollups with materialized
views can enhance performance. You can even follow up by materializing the cube or rollup. Consider the following scenario: You have the task of creating some live summary
tables for reporting January sales by salesperson and automobile type for ShinyAutoSales. Begin by understanding the existing star schema
in their data warehouse, called "sasDW," based on PostgreSQL. Then explore relevant ShinyAutoSales data
by querying the tables from the "sales" star schema in the sasDW warehouse. After exploring the schema, you decide to
create a materialized view as a staging table. Creating the view as a staging table provides
you with the data you need while minimizing your impact on the database. You can incrementally refresh the data at
will during off-peak hours. You start a PostgreSQL session and generate
an entity relationship diagram, or ERD, which represents the "Sales" star schema implemented
within the ShinyAutoSales data warehouse, "sasDW." Then, you locate the central fact table named
"fact auto sales." This table contains the "amount" column, which
is the measure you need. You also spot the three foreign keys in the
sales fact table: "sales date key," "auto class ID," and "salesperson ID." These keys link respectively to: The "Date dimension table," which contains
dates and related values such as the day of the week, month name, and quarter. The "Auto category dimension table," which
includes the "auto class name," and the Boolean "is new" column, and finally, the "Salesperson dimension table," which contains
the "salesperson's name." In this example, you are using PostgreSQL.
Let's assume you already started up the terminal-based front-end to PostgreSQL, "P S Q L," and connected
to the "S A S D W" data warehouse. Notice the command prompt contains the name
of the data warehouse you are connected to, "S A S D W." Starting with the auto sales fact table, you'll
enter the SQL statement "select star from sales dot fact auto sales limit 10" to display
its first 10 rows. Here, you see the dollar amounts for individual
auto sales, but the remaining columns are primary and foreign keys, which don't have
any direct meaning for you yet. However, you notice that the sales ID values
are sequential, but the numbering starts at 1,629 instead of 1. That's because ShinyAutoSales has provided
you with access to a windowed subset of their data. Next, you query the auto category dimension
table. Now, you can see meaningful names for various
automobile classes, such as truck and compact SUVs. You notice duplicate entries for the truck
class and wonder why they exist. When you look more closely, you realize the
duplicate entries exist because of the distinct subclasses for new and used trucks. Similarly, you generate a view for the salesperson
dimension table and find eight distinct salesperson names, including "Gocart Joe" and "Jane Honda."
So far, so good! Finally, you view the date dimension table. You notice the dates only go back to
January 1, 2021. Your contact at Shiny Auto Sales informs you
that she will provide you with more data later and that for now, you can work with a smaller
data set while you develop your queries. The date table contains potentially useful
date elements such as the day of the week, month name, and quarter name. At this stage, it would be more convenient
to have a table of data that contains the dimensions you need with human interpretable
columns, rather than just keys. Essentially, you want to create a denormalized
view of the data by joining the dimensions back to the fact of interest. You proceed by selecting the "date," "auto
class name," "is new," "salesperson name," and "amount" columns from their tables, and
joining each dimension onto the "amount" fact using an inner join on the corresponding keys. Next, why not capture the view as a materialized
view called "Denormalized sales" or "D N sales" for short? Then you can reuse the materialized view for
different queries without having to recreate your work. You accomplish this task using the clause
"CREATE MATERIALIZED VIEW D N sales AS," followed by the same query you used to generate the
denormalized view. Type "Select star from D N sales, LIMIT 10"
to display your resulting materialized view. Now you have a tidy, human-readable, time-series
of sales data available for further analysis. For example, you can see that "Cadillac Jack”
sold a new midsize SUV on January 5 for $26,500. Next, you want to apply CUBE and ROLLUP operations
to your denormalized, materialized view. Let's see the CUBE results. Here, you select the "auto class name," "salesperson
name," and the "sum of the sales amounts" from "D N sales," where "is new" is set to
"true." Finally, group the generated cube by the "auto
class name" and "salesperson name." The output looks like this: The first row has no entries in the dimensions
columns, which means 'all.' Thus, the value of $366,076 represents the total sales for
all new cars. The next block of records has both dimension
columns populated. So, for instance, you can read the total sales of new midsize
SUVs by "Gocart Joe,” which is $32,099. Similarly, the last two blocks summarize "new
auto sales" by class, and by salesperson. Next, you apply a ROLLUP instead of a CUBE
operation. You decide to keep the query the same as the previous query, except that you
replace CUBE with ROLLUP. Here's what the resulting view looks like
now. You have five fewer rows with the ROLLUP result
than CUBE, resulting in 13 rows instead of 18 rows. The only difference in this result is that
you don't have the "total sale amounts by salesperson" summary. While CUBE generates all possible permutations
of the "GROUP BY" columns, ROLLUP only looks at the single permutation defined by the columns'
order listed in the ROLLUP call. In this video, you learned that: CUBE and ROLLUP summaries on materialized
views provide powerful capabilities for quickly querying and analyzing data in data warehouses. CUBE and ROLLUP operations generate the kinds
of summaries grouped by dimensions that management often requests. You can denormalize star schemas using joins
to bring together human-interpretable facts and dimensions in a single materialized view. You can create staging tables from materialized
views, which you can incrementally refresh during off-peak hours.